﻿/*
 * 文件名：Department.cs
 * 类名：XHCTMS.DAL.Department
 * 创建人：宁娜
 * 创建时间：2013-05-20
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 对部门的操作
    /// </summary>
    public class Department
    {
        /// <summary>
        /// 增加部门
        /// </summary>
        /// <param name="dep">model层部门类的变量</param>
        /// <returns>返回值为Int类型 1/成功，0/异常，-1/失败</returns>
        public int Add(XHCTMS.MODEL.Department dep)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("insert into department values(@dep_name,@dep_desc,default)");
            SqlParameter[] para ={
                                     
                                     new SqlParameter("@dep_name",SqlDbType.NVarChar,32),
                                     new SqlParameter("@dep_desc",SqlDbType.NVarChar,128)
                                };
            
            para[0].Value = dep.DepName;
            para[1].Value = dep.DepDesc;

            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 查询所有部门并排序
        /// </summary>
        /// <returns>返回值为dataTable类型，失败返回null</returns>
        public DataTable GetDepartment()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select dep_id,dep_name,dep_desc,dep_flag from department where dep_flag=1 order by dep_name collate Chinese_PRC_CS_AS");
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString());
        }

        public DataTable GetDepartment(string depName)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select dep_id,dep_name,dep_desc,dep_flag from department where dep_name=@depName");
            SqlParameter[] para ={
                                   new SqlParameter("@depName",SqlDbType.NVarChar,32)
                                };
            para[0].Value = depName;
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString(), para);
        }
        /// <summary>
        /// 查询部门信息并分页排序
        /// </summary>
        /// <param name="pageindex">页码</param>
        /// <param name="pagesize">显示数据的数目 默认为5</param>
        /// <param name="dep_flag">部门存在状况</param>
        /// <returns>返回值为dataTable类型，失败返回null</returns>
        public DataTable GetDepartment(int pageindex,int pagesize,bool dep_flag)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select top(@pagesize) dep_id,dep_name,dep_desc,dep_flag from department where dep_flag=@dep_flag and dep_id not in
(select top((@pageindex-1)*@pagesize) dep_id from department) ORDER BY dep_name collate Chinese_PRC_CS_AS");
            SqlParameter[] para ={
                                     new SqlParameter("@pageindex",SqlDbType.Int),
                                     new SqlParameter("@pagesize",SqlDbType.Int),
                                     new SqlParameter("@dep_flag",SqlDbType.Bit)
                                };
            para[0].Value = pageindex;
            para[1].Value = pagesize;
            para[2].Value = dep_flag;
            return new XHCTMS.DBHELP.Common().GetDataTable(queryString.ToString(), para);
        }

        /// <summary>
        /// 根据部门号设置部门状态
        /// </summary>
        /// <param name="depId">部门号</param>
        /// <returns>返回值为int类型 1/成功，0/异常，-1/失败</returns>
        public int Delete(int depId)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("update department set dep_flag=0 where dep_id=@depId");
            SqlParameter[] para ={
                                     new SqlParameter("@depId",SqlDbType.Int)
                                };
            para[0].Value = depId;
            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 修改部门状态
        /// </summary>
        /// <param name="depId">部门号</param>
        /// <returns></returns>
        public int Update(int depId)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("update department set dep_flag=1 where dep_id=@depId and dep_flag=0");
            SqlParameter[] para ={
                                   new SqlParameter("@depId",SqlDbType.Int)
                                };
            para[0].Value = depId;
            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 获取部门号
        /// </summary>
        /// <param name="depName">部门名称</param>
        /// <returns></returns>
        public string GetId(string depName)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select dep_id from department where dep_name=@depName");
            SqlParameter[] para ={
                                   new SqlParameter("@depName",SqlDbType.NVarChar,32)
                                };
            para[0].Value = depName;
            return new XHCTMS.DBHELP.Common().GetDataScalar(queryString.ToString(), para);
        }
        /// <summary>
        /// 根据部门号修改正在使用的部门名称及描述
        /// </summary>
        /// <param name="dep">model层部门类的变量</param>
        /// <returns>返回值为int类型 1/成功，0/异常，-1/失败</returns>
        public int Update(XHCTMS.MODEL.Department dep)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("update department set dep_name=@depName,dep_desc=@dep_desc where dep_id=@depId and dep_flag=1");
            SqlParameter[] para ={
                                     new SqlParameter("@depName",SqlDbType.NVarChar,32),
                                     new SqlParameter("@dep_desc",SqlDbType.NVarChar,128),
                                     new SqlParameter("@depId",SqlDbType.Int)
                                };
            para[0].Value = dep.DepName;
            para[1].Value = dep.DepDesc;
            para[2].Value = dep.DepId;
            return new XHCTMS.DBHELP.Common().ExcuteQuery(queryString.ToString(), para);
        }

        /// <summary>
        /// 获取所有部门名
        /// </summary>
        /// <returns>返回dataTable类型, null/异常</returns>
        public DataTable GetDepName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select dep_id, dep_name from department");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }
    }
}
